データ変換処理をモダンな手法で開発できる「dbt」を使ってみた

奈良県でリモートワーク中の玉井です。

日本では全くと言っていいほど知名度がありませんが、国外（アメリカ？）のデータ分析界隈では既にメジャーな存在になりつつある「dbt」（data build tool）について、ご紹介します。

dbtとは？

公式情報など

公式はこちら（ググラビリティが低い名前なので検索しづらい）。

（死ぬほどざっくりいうと）データ変換を効率よく実施できるツールです。SaaSとしての提供になっているので（最初からあったわけではなく、後から登場したようです）、Webブラウザさえあれば、すぐに利用することができます。

主な特徴

ELTの「T」を担当するツール

データの前処理における作業をELT（Extract、Load、Transform）と呼称することがありますが、それの「T（変換）」を担当します。E（抽出）やL（ロード）はやりません。

そして、その変換処理をどうやって設定するのかということですが、（基本的に）SQLのSELECT文を書いて変換を実施します。欲しいデータの形をSELECT文で定義してあげると、その形のテーブルを作成してくれる感じです。

データアナリストや分析官などの、いわゆる「分析寄り」の役職の方の場合「SELECTくらいは書けるけど、それ以外のSQLとか、その他のデータ処理に関する技術スタックは持ち合わせていない」ということがあると思いますが、そういう方でも、dbtを使うと、いつものSELECTクエリを書くだけで、データ変換ができてしまいます。

Jinjaが使用できる

また、dbtは純粋なSQLだけでなく、Python用テンプレート言語の「Jinja」を組み合わせることができます。これによってSQLにロジックを持たせることができ、より柔軟なデータ変換のコードを記述することができます。

ソフトウェア開発のようにデータ変換コードをデプロイできる

昨今のアプリケーション開発手法は色々と「モダン」（？）な要素がありますよね。アジャイルだとか、自動ビルドに自動テスト、CI/CDとか…。dbtは、データ変換クエリの開発を、モダンなソフトウェア開発と同じように開発していくことができます。例えば、dbtで書いたコードは自動テストを実行できますし、テスト自体もコードで書きます。

料金

詳細は下記をどうぞ。なんと「1人で使う分には無料」！

データ分析基盤のどこらへんで使うのよ？

dbtの概要について書いてきましたが、ぶっちゃけこれだけ読んだところで「いまいちイメージがわかない」という方も多いと思います。私も実際に触るまでよくわかってなかったです。ELTの「T」って言われても、実際のデータ分析のどこらへんで使うのかよくわからない感じがしませんでしょうか。

そこで、下記の画像が参考になるかと思います。

※What, exactly, is dbt?より引用

まず、dbtの前提は「ELT」です。ETLなデータ前処理には対応していません。今日のDWHはパワーがありますので、先にDWHにロードを済ませてしまい、変換処理はDWH上で実施するという考え方です。

ですので、dbtの出番としては、「分析対象となるデータの抽出」「抽出したデータをDWHにロード」の2つが終わった後となります。要するに「DWHまでデータが持ってこれている状態」が前提ということですね。

一応、その後も説明すると、dbtでいい感じのテーブル（データマート）を作成して、それをBIツール等で参照するのが理想的な形かなと思います。

とりあえずチュートリアルやってみた

というわけで、ここからは「実際にやってみた」セクションに入ります。公式チュートリアルがあるのでそれをやってみたという格好です。

チュートリアルの内容しては、下記のような感じです。

1. Projectを作成する
2. Modelを作成する
3. テストを作成する
4. Projectをデプロイ環境にデプロイする

公式ドキュメント

環境をセットアップする

なにはともあれ、まずはdbtが使える状態にしましょう。先述した料金ページから「Developer」のプランのトライアルを開始します。トライアルといっても、元々無料のプランですので、トライアルが終わってもそのまま使えます。

で、アカウントを発行して、Webブラウザからdbtにアクセスします。dbtの画面が表示されました。

Projectを作成する

dbtで「よーし、データ変換しちゃうぞ～」ってなったら、まずやるのはProjectの作成です。ということで、実際に作成します。

New Projectを選びます。

接続するデータウェアハウスを選びます。「dbtはDWHにデータがロードされているのが前提」と書きましたが、それがすごくわかるページではないかと（一応PostgreSQLはあるけど）。ちなみに、このチュートリアルではBigQuery（以下BQ）のサンプルデータを使うので、BQを選びます。

接続先DWHの詳細設定をします。BQの場合は、サービスアカウントのキーファイルをアップロードする必要があります（dbtに限らずBQを外部アプリケーションから接続するときは大体やる設定なので詳細は割愛）。右上のTESTを押して、接続が問題ないかどうかも確認します。

このProjectで書くコードを管理するGitリポジトリを設定します。dbtには、dbt側でホスティングしているリポジトリがあるようで、それを使います（こちらで用意する必要がないので楽ちん！）。

こうしてProjectを作成することができました。

Projectを開くとこんな感じ

サンプルのModelを実行してみる

Projectを初期化する

左上の「Initialize your project」を選択すると、Projectの初期テンプレみたいなフォルダやファイルが展開されます。これを元にdbtのProjectを開発していくというわけですね。

設定ファイルを編集する

Projectの関する設定はdbt_project.ymlに記述します。先程にInitializeで基本的なことは既に書かれていますが、今回はProjectとModelの名前をjaffle_shopというものに変更してきます（下記の部分）。

name: jaffle_shop # this normally says my_new_package
...
models:
  jaffle_shop: #this normally says my_new_package. It should match the value for `name:`
    ...

とりあえずProjectを実行してみる

Project名を変えただけですが、ここで一度、dbtの基本となるdbt runをやってみます。

方法は簡単。画面下部のところにdbt runと入力して実行するだけです。既にサンプルのModelが用意されているため、そのサンプルが実行されます。ここでいうModelってなんなのよ？という話ですが、（後でも触れますが）基本はSELECT文であり、dbt runをすると、そのSELECT文で取得できる結果をそのままテーブル（またはビュー）として、接続先のDWHに作成してくれます。

runした結果は下記のように確認することが可能です。my_first_dbt_modelとmy_second_dbt_modelがそれぞれ実行されたことがわかります。

GitリポジトリにCommitする

問題なくrunできたので、変更分（といってもyamlをちょっとだけ書き直しただけですが）をコミットしましょう。画面左上のメニューから可能です。

自分でModelを開発する（そしてデプロイ）

次は実際にModelを作成してみます。

チュートリアル用のGitブランチを切る

画面左上のcreate new branchから、新しいブランチを切ることができます。

Modelファイルを作成する

作成するModelのコードを記述するファイルを用意します。modelsディレクトリ配下にcustomers.sqlというファイルを作成します。

そして、下記のクエリを、このファイルにペーストします。

with customers as (

    select
        id as customer_id,
        first_name,
        last_name

    from `dbt-tutorial`.jaffle_shop.customers

),

orders as (

    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status

    from `dbt-tutorial`.jaffle_shop.orders

),

customer_orders as (

    select
        customer_id,

        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders

    from orders

    group by 1

),


final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders

    from customers

    left join customer_orders using (customer_id)

)

select * from final

dbt runしてModelを実行する

上記のModelを作成できたら、runしてみます。Modelに記述したクエリで取得できる結果が、テーブルとして作成されます。

customersというModelが実行されています。

BigQueryを確認すると、たしかにcustomersというビューが作成されています。

生成するモデルの種類（テーブル/ビュー）を指定してみる

dbtはDWH側に生成するモデルの種類をビューかテーブルか選ぶことができます。

設定ファイルで指定する

チュートリアル冒頭で編集したdbt_project.ymlのmodelsというところに、下記のように記述することで、指定できます。

models:
  jaffle_shop:
    +materialized: table
    example:
      +materialized: view

インデントを下げたexampleというのは、Modelディレクトリ配下にあるexampleというフォルダ内にあるModelを対象とする…という意味です。つまり、上記の場合だと、Modelは基本的にはテーブルで生成するけど、example配下のModelはビューとして生成する、という感じとなります。

Modelファイルの直接記述して指定する

Project設定ファイルで全体的に指定することもできますが、Modelファイルに直接指定を定義することもできます。2つの指定が競合したときは、ファイル直接指定の方が優先されます。

試しにmy_first_dbt_modelに下記を記述します。これでmy_first_dbt_modelはテーブルとして生成されるようになりました。

{{
  config(
    materialized='table'
  )
}}

dbt runしてModelを実行する

スクリーンショットを撮り忘れた上記の設定後、実際にrunしてみます（dbt runの後に--full-refreshオプションを付与する必要があります）。

そしてrunした結果が下記の通り。

BigQuery側でも確認。example下のModelが基本的にはビューですが、my_first_dbt_modelはテーブルで生成するように直接指定しているので下記のようになりました。

Modelを分割する

作成したcustomersというModelは、WITH句の中で、複数のSELECT文が使われています。これでも問題は無いのですが、長いクエリは別々に分割した方が、可読性や管理のし易さ等が向上します。

WITH句で生成していたModelを抜き出して、新しいModelファイルを作成する

下記のModelをそれぞれ作成します（Modelディレクトリに作成する）。

stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name

from `dbt-tutorial`.jaffle_shop.customers

stg_orders.sql

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from `dbt-tutorial`.jaffle_shop.orders

顧客テーブルと注文テーブルを、それぞれ分割して用意した形となります。

元々のModelファイルを編集する

WITH句で行っていた2つのSELECT文を、別々のModelファイルとして用意しました。ということで、元々のcustomers.sqlを下記のように変更します。

with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('stg_orders') }}

),

customer_orders as (

    select
        customer_id,

        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders

    from orders

    group by 1

),


final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders

    from customers

    left join customer_orders using (customer_id)

)

select * from final

{{ ref('xxxxx') }}と記述することで、そのModelの結果を、そのまま引き継ぐことができます。ちなみに、依存関係（どちらが先に実行されるか？的なこと）を明確に記述する必要はありません。

dbt runしてModelを実行する

上記の状態でrunすると、stg~の2つのテーブルと、それを利用したcustomersテーブルが作成されます。

Modelをテストする

テストの設定ファイルを作成する

dbtは、作成したModelファイルに対してテストを実行することができます。テストもコード（YAML）で記述します。

Modelディレクトリ配下にschema.ymlを作成し、下記のように記述します。

version: 2

models:
  - name: customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: stg_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

各Modelに対して「XというカラムにNULLがないかどうか」「YとZのModel間のリレーションシップは間違ってないか」といったテストを記述することができます（読めば大体の内容はわかるかと思います）。

テストを実行する

テストは、dbt testを実行します。

それぞれのテスト結果がわかります。テスト用のクエリが実行されるのですが、その時のエラーが0件であれば、テストは通過したといえます。

テストに説明を追加する

テストファイルには、descriptionというパラメータを使って、詳細な説明を記述することができます。

version: 2

models:
  - name: customers
    description: One record per customer
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: first_order_date
        description: NULL when a customer has not yet placed an order.

  - name: stg_customers
    description: This model cleans up customer data
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null

  - name: stg_orders
    description: This model cleans up order data
    columns:
      - name: order_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

こういう記述を行うことで、何のためにどういう内容のテストを行っているのかを、自分以外のメンバーにわかりやすく伝えることができます。

Projectをデプロイ環境にデプロイする

一通りdbtのProjectを作成し終えて、「これらのModel生成を定期的に実行するようにして、本番運用したい」となれば、いよいよデプロイとなります。

変更をすべてコミットする

画面左上の緑色のボタンから変更内容をブランチにコミットします。コミット後、それらをMasterブランチにマージします（merge to masterボタンを押す）。

デプロイ環境を作成する

Masterブランチを最新にしたら、いよいよデプロイ環境を作成します。

画面左上のハンバーガーメニューからHomeをクリックします。

「Deployment Environments」下の「Create a new one」をクリックして、新規デプロイ環境を作成します。

色々設定項目がありますが、最低限、「デプロイ環境の名称（NAME）」と「Target Dataset（デプロイ環境がModelを作成するデータセット。BigQuery以外だとスキーマのこと）」を設定します。

ジョブを作成する

このデプロイ環境で実行するdbtコマンドやスケジューリング等をジョブとして設定します。

作成したデプロイ環境のぺージから、New Jobを選びます。

ジョブの設定画面が出るので色々設定します。デプロイ環境で実行するコマンドはdbt runと dbt testですので、それが追加されてるかどうか確認します。後は、スケジューリング機能（デプロイ環境の定期実行）がありますが、今回はチュートリアルなのでOFFにします（定期実行したらBigQueryの無料枠が死んじゃう）。

一通り設定し終えたら、Saveして、ジョブを実際に動かしてみます。ジョブ画面のRun nowをクリックすると、ジョブが動きます。

これで、Project作成～デプロイ環境でジョブを作成して実行…までを一通り終えました。

おわりに

データ分析界における最新のSaaSですが、全く新しい何かをするサービスというよりは、既存のデータ変換作業（SQLを書きまくってデータ処理を実装する感じ）を楽に行えるサービスという感じでした。冒頭でも述べましたが、泥臭いデータ処理の開発に、最新のソフトウェア開発の手法を取り入れたサービスかと思います。

他にも色々な機能があるので（何よりJinjaをまだ触っていない）、引き続きdbtもブログを増やしていきたいと思います。